package com.gxan.dao.impl;

import com.gxan.dao.RecruitDao;
import com.gxan.pojo.Recruit;
import com.gxan.pojo.Resume;
import com.gxan.pojo.SysDept;
import com.gxan.pojo.SysJob;
import com.gxan.utils.db.JdbcUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class RecruitDaoImpl extends JdbcUtils implements RecruitDao {
    @Override
    public List<Recruit> findAllPosts(Integer offset, Integer limit) throws SQLException {
        String sql = "SELECT a.*,b.dept_short_name,c.job_name,c.job_type\n" +
                "FROM recruit AS a\n" +
                "JOIN sys_dept AS b ON a.dept_id = b.dept_id\n" +
                "JOIN sys_job AS c ON c.job_id = a.job_id\n" +
                "limit ?,?";
        Object[] params = {offset,limit};

        return super.queryListEntity(Recruit.class,sql,params);
    }

    @Override
    public Integer countAll() throws SQLException {
        String sql = "SELECT count(*) from recruit";
        return super.findAllForInt(sql);
    }

    @Override
    public int delPostById(Integer recruitId) throws SQLException {
        String sql = "delete from recruit where recruit_id = ?";
        return super.executeUpdate(sql,recruitId);
    }

    @Override
    public int updatePost(Recruit recruit) throws SQLException {
        String sql = "update recruit set recruit_type= ?, job_id = ?, resolution = ?, regist_time = ?, deadline = ?, `require` = ?, dept_id = ?, recruit_number = ?, description = ? where recruit_id = ?";
        Object[] params = {recruit.getRecruit_type(),recruit.getJob_id(),recruit.getResolution(),recruit.getRegist_time(),recruit.getDeadline(),recruit.getRequire(),recruit.getDept_id(),recruit.getRecruit_number(),recruit.getDescription(),recruit.getRecruit_id()};
        return super.executeUpdate(sql,params);
    }

    @Override
    public int addPost(Recruit recruit) throws SQLException {
        String sql = "INSERT INTO recruit VALUE(NULL,?,?,?,?,?,?,?,?,?)";
        Object[] params = {recruit.getRecruit_type(),recruit.getJob_id(),recruit.getResolution(),recruit.getRegist_time(),recruit.getDeadline(),recruit.getRequire(),recruit.getDept_id(),recruit.getRecruit_number(),recruit.getDescription()};

        return super.executeUpdate(sql,params);
    }

    @Override
    public List<Recruit> searchPost(String jobName, String jobType, String time1, String time2, Integer offset, Integer limit) throws SQLException {
        String sql = "SELECT a.*,b.dept_short_name,c.job_name,c.job_type\n" +
                "FROM recruit AS a\n" +
                "JOIN sys_dept AS b ON a.dept_id = b.dept_id\n" +
                "JOIN sys_job AS c ON c.job_id = a.job_id\n" +
                "where c.job_name like concat('%',?,'%')" ;
        List<Object> params = new ArrayList<>();
        params.add(jobName);
        if (jobType != null && !jobType.isEmpty()){
            sql = sql + "and c.job_type = ?";
            params.add(jobType);
        }
        if (time1 != null && !time1.isEmpty()){
            sql = sql + "and a.regist_time >= ?";
            params.add(time1);
        }
        if (time2 != null && !time2.isEmpty()){
            sql = sql + "and a.deadline <= ?";
            params.add(time2);
        }
        sql = sql + "limit ?,?";
        params.add(offset);
        params.add(limit);
        return super.queryListEntity(Recruit.class,sql,params.toArray());
    }

    @Override
    public int countSearch(String jobName, String jobType, String time1, String time2) throws SQLException {
        String sql = "SELECT count(*) FROM " +
                "recruit AS a " +
                "JOIN sys_dept AS b ON a.dept_id = b.dept_id " +
                "JOIN sys_job AS c ON c.job_id = a.job_id " +
                "WHERE c.job_name LIKE CONCAT('%', ?, '%')" ;
        List<Object> params = new ArrayList<>();
        params.add(jobName);
        if (jobType != null && !jobType.isEmpty()){
            sql = sql + "and c.job_type = ?";
            params.add(jobType);
        }
        if (time1 != null && !time1.isEmpty()){
            sql = sql + "and a.regist_time >= ?";
            params.add(time1);
        }
        if (time2 != null && !time2.isEmpty()){
            sql = sql + "and a.deadline <= ?";
            params.add(time2);
        }
        return super.findAllForInt(sql,params.toArray());
    }

    @Override
    public List<Resume> findAllResume(Integer offset, Integer limit) throws SQLException {
        String sql = "SELECT a.*,c.job_name,c.job_type\n" +
                "FROM `resume` AS a\n" +
                "JOIN sys_job AS c ON a.job_id = c.job_id\n" +
                "limit ?,?";
        Object[] params = {offset,limit};
        return super.queryListEntity(Resume.class,sql,params);
    }

    @Override
    public Integer countAllResume() throws SQLException {
        String sql = "SELECT count(*)\n" +
                "FROM `resume` AS a\n" +
                "JOIN sys_job AS c ON a.job_id = c.job_id";
        return super.findAllForInt(sql);
    }

    @Override
    public int addResume(Resume resume) throws SQLException {
        String sql = "insert into `resume` value(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)";
        Object[] params ={resume.getIdentity(),resume.getName(),resume.getJob_id(),resume.getSex(),resume.getRecruit_type(),resume.getLocation(),resume.getAddress(),
                            resume.getPhone(),resume.getEmail(),resume.getPolitic_outlook(),resume.getMajor(),resume.getGraduate_school(), resume.getDegree(),resume.getExperience(),
                            resume.getSalary(),resume.getIs_work(),resume.getIs_graduate(),resume.getReg_time(),resume.getResumes(),resume.getResume_url(),resume.getIs_recom(),
                            resume.getRecommender(),resume.getRecom_time(),resume.getRecom_advice()};
        return super.executeUpdate(sql,params);
    }


    @Override
    public int updateResume(Resume resume) throws SQLException {
        String sql = "update `resume` set identity = ?, name = ?, job_id = ?, sex = ?, recruit_type = ?, location = ?, address = ?,phone = ?," +
                "email = ?, politic_outlook = ?, major = ?, graduate_school = ?, degree = ?, experience = ?, salary = ?, is_work = ?, is_graduate = ?," +
                "reg_time = ?, resumes = ?, resume_url = ?, is_recom = ?, recommender = ?, recom_time = ?, recom_advice = ? where resume_id = ?";
        Object[] params ={resume.getIdentity(),resume.getName(),resume.getJob_id(),resume.getSex(),resume.getRecruit_type(),resume.getLocation(),resume.getAddress(),
                resume.getPhone(),resume.getEmail(),resume.getPolitic_outlook(),resume.getMajor(),resume.getGraduate_school(), resume.getDegree(),resume.getExperience(),
                resume.getSalary(),resume.getIs_work(),resume.getIs_graduate(),resume.getReg_time(),resume.getResumes(),resume.getResume_url(),resume.getIs_recom(),
                resume.getRecommender(),resume.getRecom_time(),resume.getRecom_advice(),resume.getResume_id()};
        return super.executeUpdate(sql,params);
    }

    @Override
    public List<Resume> searchResume(String name, String jobName, String experience, String status, String regTime, Integer offset, Integer limit) throws SQLException {
        String sql = "select a.*,b.job_name,b.job_type\n" +
                "from `resume` as a\n" +
                "join sys_job as b on a.job_id = b.job_id\n" +
                "where a.name like concat('%',?,'%')";
        List<Object> params = new ArrayList<>();
        params.add(name);
        if (jobName != null && !jobName.isEmpty()){
            sql = sql + "and b.job_name = ?";
            params.add(jobName);
        }
        if (experience != null && !experience.isEmpty()){
            sql = sql + "and a.experience >= ?";
            params.add(experience);
        }
        if (status != null && !status.isEmpty()){
            sql = sql + "and a.status = ?";
            params.add(status);
        }
        if (regTime != null && !regTime.isEmpty()){
            sql = sql + "and a.reg_time = ?";
            params.add(regTime);
        }
        sql = sql + "limit ?,?";
        params.add(offset);
        params.add(limit);
        return super.queryListEntity(Resume.class,sql,params.toArray());
    }

    @Override
    public int countSearchResume(String name, String jobName, String experience, String status, String regTime) throws SQLException {
        String sql = "select count(*)\n" +
                "from `resume` as a\n" +
                "join sys_job as b on a.job_id = b.job_id\n" +
                "where a.name like concat('%',?,'%')";
        List<Object> params = new ArrayList<>();
        params.add(name);
        if (jobName != null && !jobName.isEmpty()){
            sql = sql + "and b.job_name = ?";
            params.add(jobName);
        }
        if (experience != null && !experience.isEmpty()){
            sql = sql + "and a.experience >= ?";
            params.add(experience);
        }
        if (status != null && !status.isEmpty()){
            sql = sql + "and a.status = ?";
            params.add(status);
        }
        if (regTime != null && !regTime.isEmpty()){
            sql = sql + "and a.reg_time = ?";
            params.add(regTime);
        }
        return super.findAllForInt(sql,params.toArray());
    }

    @Override
    public int addInterview(Integer resumeID) throws SQLException {
        String sql = "insert into interview (resume_id) value(?)";

        return super.executeUpdate(sql,resumeID);
    }

    @Override
    public int updateInterview(Resume resume) throws SQLException {
        String sql = "update interview set interviewer_one = ?, one_time = ?, one_evaluate = ?," +
                "interviewer_two = ?, two_time = ?, two_evaluate = ? , " +
                "interviewer_three = ?, three_time = ?, three_evaluate = ? where interview_id = ?";
        Object[] params = {resume.getInterviewer_one(),resume.getOne_time(),resume.getOne_evaluate(),resume.getInterviewer_two(),resume.getTwo_time(),
                resume.getTwo_evaluate(),resume.getInterviewer_three(),resume.getThree_time(),resume.getThree_evaluate(),resume.getInterview_id()};
        return super.executeUpdate(sql,params);
    }

    @Override
    public int addHire(Integer resumeId,Integer interviewId) throws SQLException {
        String sql = "insert into hired (resume_id,interview_id) value(?,?)";
        Object[] params = {resumeId,interviewId};
        return super.executeUpdate(sql,params);
    }

    @Override
    public int updateStatus(String status,Integer resumeId) throws SQLException {
        String sql = "update `resume` set status = ? where resume_id = ?";
        Object[] params = {status,resumeId};
        return super.executeUpdate(sql,params);
    }

    @Override
    public int updateHire(Resume resume) throws SQLException {
        String sql = "update hired set time = ? ,resolution = ? where id = ?";
        Object[] params = {resume.getTime(),resume.getResolution(),resume.getId()};
        return super.executeUpdate(sql,params);
    }

    @Override
    public int delResumeById(Integer resumeId) throws SQLException {
        String sql = "delete from `resume` where resume_id = ?";
        return super.executeUpdate(sql,resumeId);
    }


    @Override
    public List<Resume> findAllInterview(Integer offset, Integer limit) throws SQLException {
        String sql = "SELECT a.*,c.job_name,c.job_type,e.interview_id,e.interviewer_one,e.interviewer_two,e.interviewer_three,e.one_time,e.two_time,e.three_time,e.one_evaluate,e.two_evaluate,e.three_evaluate\n" +
                "FROM `resume` AS a\n" +
                "JOIN sys_job AS c ON a.job_id = c.job_id\n" +
                "JOIN interview AS e ON a.resume_id = e.resume_id\n" +
                "where a.status > 0\n" +
                "limit ?,?";
        Object[] params = {offset,limit};
        return super.queryListEntity(Resume.class,sql,params);
    }

    @Override
    public Integer countAllInterview() throws SQLException {
        String sql = "SELECT count(*)\n" +
                "FROM `resume` AS a\n" +
                "JOIN sys_job AS c ON a.job_id = c.job_id\n" +
                "JOIN interview AS e ON a.resume_id = e.resume_id\n" +
                "where a.status > 0" ;
        return super.findAllForInt(sql);
    }

    @Override
    public List<Resume> findAllHire(Integer offset, Integer limit) throws SQLException {
        String sql = "SELECT a.*,c.job_name,c.job_type,e.interviewer_one,e.interviewer_two,e.interviewer_three,e.one_time,e.two_time,e.three_time,e.one_evaluate,e.two_evaluate,e.three_evaluate,d.id,d.time,d.resolution\n" +
                "FROM `resume` AS a\n" +
                "JOIN sys_job AS c ON a.job_id = c.job_id\n" +
                "JOIN interview AS e ON a.resume_id = e.resume_id\n" +
                "JOIN hired AS d ON a.resume_id = d.resume_id\n" +
                "where a.status > 3\n" +
                "limit ?,?";
        Object[] params = {offset,limit};
        return super.queryListEntity(Resume.class,sql,params);
    }

    @Override
    public Integer countAllHire() throws SQLException {
        String sql = "SELECT count(*)\n" +
                "FROM `resume` AS a\n" +
                "JOIN sys_job AS c ON a.job_id = c.job_id\n" +
                "JOIN interview AS e ON a.resume_id = e.resume_id\n" +
                "JOIN hired AS d ON a.resume_id = d.resume_id\n" +
                "where a.status > 3" ;
        return super.findAllForInt(sql);
    }

    @Override
    public List<SysJob> getJobName() throws SQLException {
        String sql = "select * from sys_job where status = 1";
        return super.queryListEntity(SysJob.class,sql);
    }

    @Override
    public List<SysDept> getDeptName() throws SQLException {
        String sql = "select * from sys_dept";
        return super.queryListEntity(SysDept.class,sql);
    }

}
